/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webproject.member;

import com.webproject.jsp.common.JDriverManager;
import com.webproject.member.model.Users;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author jeong
 */
public class UsersManager {
    
    public static UsersManager instance = new UsersManager();
    
    public static UsersManager getInstance() {
        
        return instance;
    }
    
    public UsersManager() {
        
    }
    
//    public Connection getConnection() throws SQLException {
//        return DriverManager.getConnection(
//            "com.mysql.jdbc.Driver" );
//    }
    /**
     * 
     * @param users
     * @throws UsersManagerException 
     */
    public void insertUsers(Users users) throws UsersManagerException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement(
                    "insert into USERS(user_id, user_password"
                    + ",user_name, user_sex, user_email, user_address, user_avatar"
                    + ",reg_user_id, reg_date) values(?, ?, ?, ?, ?, ?, ?, ?, now()) ");
            
            pstmt.setString(1, users.getUserId());
            pstmt.setString(2, users.getUserPassword());
            pstmt.setString(3, users.getUserName());
            pstmt.setString(4, users.getUserSex());
            pstmt.setString(5, users.getEmail());
            pstmt.setString(6, users.getAddress());
            pstmt.setString(7, users.getAvatar());
            pstmt.setString(8, users.getUserId());
            
            pstmt.executeUpdate();
            
        } catch (SQLException se) {
            
            throw new UsersManagerException("insertUsers",se);
            
        } finally {
            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
            
        }
        
    }
    /**
     * 
     * @param userId
     * @return
     * @throws UsersManagerException 
     */
    public Users getUsers(String userId) throws UsersManagerException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            
            conn = JDriverManager.getConnection();
            
            pstmt = conn.prepareStatement(
                    "SELECT user_id, user_password, user_password"
                  + ", user_name, user_sex, user_email, user_address, user_avatar"
                  + ", reg_user_id, reg_date"
                  + "FROM users WHERE user_id = ?");
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            
            if(rs.next()) {
                Users user = new Users();
                user.setUserId(rs.getString("USER_ID"));
                user.setUserPassword(rs.getString("USER_PASSWORD"));
                user.setUserName(rs.getString("USER_NAME"));
                user.setUserSex(rs.getString("USER_SEX"));
                user.setEmail(rs.getString("USER_EMAIL"));
                user.setAddress(rs.getString("USER_ADDRESS"));
                user.setAvatar("USER_AVATAR");
                user.setRegUserId("REG_USER_ID");
                user.setRegDate("REG_DATE");
                
                return user;                
            } else {
                
                return null;
            }
            
        } catch(SQLException se) {
            
            throw new UsersManagerException("getUsers", se);
            
        } finally {
            if(rs != null) try { rs.close(); } catch(SQLException ex) {}            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}            
        }
    }
    /**
     * 
     * @param user
     * @throws UsersManagerException 
     */
    public void updateUsers(Users user) throws UsersManagerException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement(
                    "UPDATE USERS SET USER_NAME = ?, USER_SEX = ?, "
                    + "USER_EMAIL = ?, USER_ADDRESS = ?"
                  + ", USER_AVATAR = ?, UPD_USER_ID = ?, UPD_DATE = now()");
            pstmt.setString(1, user.getUserName());
            pstmt.setString(2, user.getUserSex());
            pstmt.setString(3, user.getEmail());
            pstmt.setString(4, user.getAddress());
            pstmt.setString(5, user.getAvatar());
            pstmt.setString(6, user.getUpdUserId());
            //pstmt.setString(7, user.getUpdDate());
            
            int  result = pstmt.executeUpdate();
            if(result == 0) {
                throw new UsersManagerException("존재하지 않는 ID");
            }
            
        } catch (SQLException se) {
            throw new UsersManagerException("updateUsers",se); 
        } finally {
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
        }
    }
    /**
     * 
     * @param userid
     * @throws UsersManagerException 
     */
    public void deleteUsers(String userid) throws UsersManagerException, Exception {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement("DELETE FROM USERS WHERE USER_ID = ?");
            pstmt.setString(1, userid);
            
            int result = pstmt.executeUpdate();
            if(result == 0) {
                throw new UsersManagerException("존재하지 않는 ID");
            }
            
        } catch (SQLException se) {
            throw new UsersManagerException("deleteUsers", se);
        } finally {
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}            
        }    
    }
    /**
     * 
     * @param userId
     * @param password
     * @return
     * @throws UsersManagerException 
     */
    public int checkAuth(String userId, String password) throws UsersManagerException, Exception {
    
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = JDriverManager.getConnection();
            
            pstmt = conn.prepareStatement("SELECT user_password FROM USERS WHERE user_id = ?");
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String dbPassword = rs.getString("user_password");
                if(dbPassword.compareTo(password) == 0) {
                    // 암호가 같으면 1을 리턴.
                    return 1;
                } else {
                    // 암호가 다르면 0을 리턴.
                    return 0;
                }
            } else {
                // 아이디 존재하지 않음 -1
                return -1;
            }

        } catch(SQLException se) {
            throw new UsersManagerException("checkAuth",se);
        } finally {
            if(rs != null) try { rs.close(); } catch(SQLException ex) {}            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
        }
    }
    
}
